package services;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;

import javax.jws.WebService;
import model.*;

@WebService
public class Service {

	databaseConnection.Connection cn = new databaseConnection.Connection();

	/**
	 * Sign In Function it will take username and password as parameters and
	 * will return a string with validation "true_1 || true_2 || true_3" // any
	 * one can populate as after delimiter the value denotes the type of //
	 * customer 1)Administrator 2)Premium Member and 3)Simple User // for
	 * driving the jsp to their respective content in the jsp. // returning only
	 * "false" will denote invalid login no such loginid exists // in DB //
	 * returning "Invalid Username" means username is not correct and is not //
	 * upto the username policy. // returning "Invalid Password" means password
	 * is not correct and is not // upto the password policy.
	 * 
	 * @param username
	 * @param password
	 * @return User
	 * @throws SQLException
	 */
	public User signIn(String username, String password) throws SQLException {

		User user = new User();
		user.setErrorMsg("valid");
		Preference preference = new Preference();
		Status status = new Status();
		/**
		 * Validation begins
		 */
		if (!Validation.validAlphaNumericString(username)) {
			user.setErrorMsg("Invalid Login ID");
			System.out.println(user.getErrorMsg());
		}
		if (!Validation.validPasswordString(password)) {
			user.setErrorMsg("Invalid Password");
			System.out.println(user.getErrorMsg());
		}
		if (("valid").equals(user.getErrorMsg())) {
			System.out
					.println("Username and password is valid! Authenticating....");
			Connection con = cn.getConnection();
			ResultSet rs = null;
			PreparedStatement pst = null;
			try {
				pst = con
						.prepareStatement("select vlusr_id,vlusr_displayname,vlusr_prefernce,"
								+ "vlusr_loginid,vlusr_password,vlusr_invalidlogins,vlusr_statusid from "
								+ constants.ConstantData.SCHEMA_NAME
								+ ".VideoLibUsers where vlusr_loginid = ? and vlusr_rowstate != -1");
				pst.setString(1, username);
				rs = pst.executeQuery();

				while (rs.next()) {
					user.setId(rs.getInt(1));
					user.setDisplayName(rs.getString(2));
					preference.setPreferenceId(rs.getInt(3));
					user.setPreference(preference);
					user.setLoginId(rs.getString(4));
					user.setPassword(rs.getString(5));
					user.setInvalidLogins(rs.getInt(6));
					status.setStatusId(rs.getInt(7));
					user.setStatus(status);
				}
				if (user.getId() != 0) {
					if (!(user.getLoginId() == null)) {
						if (user.getInvalidLogins() <= 10
								&& user.getStatus().getStatusId() == 1) {
							if ((user.getLoginId().equals(username))
									&& (user.getPassword().equals(password))) {
								user.setErrorMsg(constants.ConstantData.SUCCESS);
							} else {
								invalidLogin(username);
								user.setErrorMsg("Invalid Combination (Try Again with correct Username/password)");
								System.out.println("Error msg set : "
										+ user.getErrorMsg());
							}
						}
					}
				} else
					user.setErrorMsg("Invalid Combination (Try Again with correct Username/password)");

			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				rs.close();
				pst.close();
				con.close();
			}
		}
		System.out.println("test");
		// System.out.println("User Pref ID :"
		// + user.getPreference().getPreferenceId());
		// System.out.println("User ID :" + user.getId());
		System.out.println("User Error message :" + user.getErrorMsg());
		return user;
	}

	public void invalidLogin(String loginid) throws SQLException {
		java.util.Date today = new java.util.Date();
		java.sql.Date sqlToday = new java.sql.Date(today.getTime());
		PreparedStatement pst = null;
		Connection con = cn.getConnection();
		try {
			pst = con
					.prepareStatement("update  "
							+ constants.ConstantData.SCHEMA_NAME
							+ ".VideoLibUsers set vlusr_invalidlogins = vlusr_invalidlogins+1 , vlusr_lastdetailsupdate = ? where vlusr_loginid = ? and vlusr_rowstate != -1");
			pst.setDate(1, sqlToday);
			pst.setString(2, loginid);
			pst.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			pst.close();
			con.close();
			cn.getDisconnection();
		}
	}

	/**
	 * Inserts the new User data
	 * 
	 * @param user
	 * @return
	 * @throws SQLException
	 */
	public String signUp(User user) throws SQLException {
		Connection con = cn.getConnection();
		PreparedStatement pst = null;
		String state = "false";
		boolean error = false;
		java.util.Date today = new java.util.Date();
		java.sql.Date sqlToday = new java.sql.Date(today.getTime());

		if (user.getSalutation() != null)
			if (!Validation.validAlphaString(user.getSalutation())) {
				System.out.println("Invalid Salutation");
				state = state.concat(" Invalid Salutation");
				error = true;
			}

		if (!Validation.validAlphaString(user.getFirstName())) {
			System.out.println("Invalid First Name");
			state = state.concat(" Invalid First name");
			error = true;
		}

		if (user.getMiddleName() != null) {
			System.out.println(user.getMiddleName());
			if (!Validation.validAlphaString(user.getMiddleName())) {
				System.out.println("Invalid Middle Name");
				state = state.concat(" Invalid Middle name");
				error = true;
			}
		}

		if (!Validation.validAlphaString(user.getLastName())) {
			System.out.println("Invalid LastName");
			state = state.concat(" Invalid Last name");
			error = true;
		}

		if (!Validation.validAlphaString(user.getDisplayName())) {
			System.out.println("Invalid Display Name");
			state = state.concat(" Invalid Display name");
			error = true;
		}

		if (!Validation.validDateFormatString(user.getDob())) {
			System.out.println("Invalid Date of Birth(dd/mm/yyyy)");
			state = state
					.concat(" Invalid date of birth(Valid Format - dd/mm/yyyy)");
			error = true;
		}
		if (user.getSex() != null) {
			if (!Validation.validAlphaString(user.getSex())) {
				System.out.println("Invalid Gender/Sex");
				state = state.concat(" Invalid Gender/Sex");
				error = true;
			}
		} else {
			state = state.concat(" Invalid Gender/Sex");
			error = true;
		}

		if (!Validation.validEmailString(user.getEmailId())) {
			System.out.println("Invalid Email ID");
			state = state.concat(" Invalid Email ID");
			error = true;
		}

		if (!Validation.validAlphaNumericString(user.getLoginId())) {
			System.out.println("Invalid LoginId");
			state = state.concat(" Invalid Login ID");
			error = true;
		}
		if (!Validation.validPasswordString(user.getPassword())) {
			System.out.println("Invalid Password");
			state = state.concat(" Invalid Password");
			error = true;
		}

		user.getPreference().setPreferenceId(
				getpreferenceID(user.getPreference()));
		user.getAddress().getCountry()
				.setCountryId(getCountryID(user.getAddress().getCountry()));
		user.getAddress()
				.getState()
				.setStateId(
						getStateID(user.getAddress().getState(), user
								.getAddress().getCountry()));
		user.getAddress()
				.getCity()
				.setCityId(
						getCityID(user.getAddress().getState(), user
								.getAddress().getCity(), user.getAddress()
								.getCountry()));

		if (user.getPreference().getPreferenceId() == 2) {
			if (!Validation.validSSNFormatString(user.getSsnId())) {
				System.out.println("Invalid SSN");
				state = state.concat(" Invalid SSN");
				error = true;
			}
		} else
			user.setSsnId(null);

		if (!Validation.validAddressString(user.getAddress().getAddrLine1())) {
			System.out.println("Invalid Address Line 1");
			state = state.concat(" Invalid Address Line 1");
			error = true;
		}

		if (user.getAddress().getAddrLine2() != null) {
			if (!Validation
					.validAddressString(user.getAddress().getAddrLine2())) {
				System.out.println("Invalid Address Line 2");
				state = state.concat(" Invalid Address Line 2");
				error = true;
			}
		} else
			user.getAddress().setAddrLine2(null);

		if (user.getAddress().getAddrLine3() == null) {
			if (!Validation
					.validAddressString(user.getAddress().getAddrLine3())) {
				System.out.println("Invalid Address Line 3");
				state = state.concat(" Invalid Address Line 3");
				error = true;
			}
		} else
			user.getAddress().setAddrLine3(null);

		if (!Validation.validNumericString(user.getAddress().getTelNo())) {
			System.out.println("Invalid Phone Number");
			state = state.concat(" Invalid Phone Number");
			error = true;
		}

		if (!Validation.validZipString(String.valueOf(user.getAddress()
				.getZipCode()))) {
			System.out.println("Invalid ZIP");
			state = state.concat(" Invalid ZIP");
			error = true;
		}
		System.out.println("testing 2");
		if (error)
			return state;
		else {
			try {
				pst = con
						.prepareStatement(constants.ConstantData.INSERT_VIDEOLIBUSERS);
				pst.setString(1, user.getSalutation());
				pst.setString(2, user.getFirstName());
				pst.setString(3, user.getMiddleName());
				pst.setString(4, user.getLastName());
				pst.setString(5, user.getDisplayName());
				pst.setString(6, user.getDob());
				pst.setString(7, user.getSex());
				pst.setInt(8, insertAddress(user.getAddress()));
				pst.setString(9, user.getLoginId());
				pst.setString(10, user.getPassword());
				pst.setDate(11, sqlToday);
				pst.setInt(12, constants.ConstantData.ACTIVE_STATUS_ID);
				pst.setInt(13, user.getPreference().getPreferenceId());
				pst.setString(14, user.getEmailId());
				pst.setInt(15, 0);
				pst.setDate(16, null);
				pst.setString(17, user.getSsnId());
				pst.setInt(18, user.getSubscriptionFee());
				pst.setDate(19, null);
				pst.setDate(20, sqlToday);
				pst.setDate(21, null);
				pst.setInt(22, 1);
				pst.executeUpdate();
				state = "true";
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				pst.close();
				con.close();
				cn.getDisconnection();
			}
		}
		return state;
	}

	public int getpreferenceID(Preference pref) throws SQLException {
		int prefId = 0;
		Connection con = cn.getConnection();
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			pst = con
					.prepareStatement("select vlusrtype_id from "
							+ constants.ConstantData.SCHEMA_NAME
							+ ".videolibusertypemaster where vlusrtype_name = ? and vlusrtype_statusid = 1 and"
							+ " vlusrtype_rowstate != -1");
			pst.setString(1, pref.getPreferenceType());
			rs = pst.executeQuery();
			while (rs.next()) {
				prefId = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			pst.close();
			rs.close();
			con.close();
		}
		return prefId;
	}

	public int getCountryID(Country country) throws SQLException {
		int countryId = 0;
		Connection con = cn.getConnection();
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			pst = con
					.prepareStatement("select vlc_countryid from "
							+ constants.ConstantData.SCHEMA_NAME
							+ ".videolibcountrymaster where vlc_countryname = ? and vlc_rowstate != -1");
			pst.setString(1, country.getCountryName());
			rs = pst.executeQuery();
			while (rs.next()) {
				countryId = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			pst.close();
			rs.close();
			con.close();
		}
		return countryId;
	}

	public int getStateID(State state, Country country) throws SQLException {
		int stateId = 0;
		Connection con = cn.getConnection();
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			pst = con
					.prepareStatement("select vls_stateid from "
							+ constants.ConstantData.SCHEMA_NAME
							+ ".videolibstatemaster where vls_statename = ? and vls_countryid = ? and vls_rowstate != -1");
			pst.setString(1, state.getStateName());
			pst.setInt(2, getCountryID(country));
			rs = pst.executeQuery();
			while (rs.next()) {
				stateId = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			pst.close();
			rs.close();
			con.close();
		}
		return stateId;
	}

	public int getCityID(State state, City city, Country country)
			throws SQLException {
		int cityId = 0;
		Connection con = cn.getConnection();
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			pst = con
					.prepareStatement("select vlcm_cityid from "
							+ constants.ConstantData.SCHEMA_NAME
							+ ".videolibcitymaster where vlcm_cityname = ? and vlcm_stateid = ? and vlcm_rowstate != -1");
			pst.setString(1, city.getCityName());
			pst.setInt(2, getStateID(state, country));
			rs = pst.executeQuery();
			while (rs.next()) {
				cityId = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			pst.close();
			rs.close();
			con.close();
		}
		return cityId;
	}

	/**
	 * Inserts the address of the user and returns the new generated addressID
	 * 
	 * @param address
	 * @return
	 * @throws SQLException
	 */
	public int insertAddress(Address address) throws SQLException {

		java.util.Date today = new java.util.Date();
		java.sql.Date sqlToday = new java.sql.Date(today.getTime());
		PreparedStatement pst = null;
		ResultSet rs = null;
		int cityid = 0, countryid = 0, stateid = 0, maxAddrId = 1;
		Connection con = cn.getConnection();
		try {

			pst = con
					.prepareStatement("select vlcm_cityid from "
							+ constants.ConstantData.SCHEMA_NAME
							+ ".VideoLibCityMaster where vlcm_cityname = ? and vlcm_rowstate != -1");
			pst.setString(1, address.getCity().getCityName());
			rs = pst.executeQuery();
			while (rs.next()) {
				cityid = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		try {
			con = cn.getConnection();
			rs = null;
			pst = con
					.prepareStatement("select vls_stateid from "
							+ constants.ConstantData.SCHEMA_NAME
							+ ".VideoLibStateMaster where vls_statename = ? and vls_rowstate != -1");
			pst.setString(1, address.getState().getStateName());
			rs = pst.executeQuery();
			while (rs.next()) {
				stateid = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		try {
			con = cn.getConnection();
			rs = null;
			pst = con
					.prepareStatement("select vlc_countryid from "
							+ constants.ConstantData.SCHEMA_NAME
							+ ".VideoLibCountryMaster where vlc_countryname = ? and vlc_rowstate != -1");
			pst.setString(1, address.getCountry().getCountryName());
			rs = pst.executeQuery();
			while (rs.next()) {
				countryid = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		try {
			con = cn.getConnection();
			pst = con
					.prepareStatement(constants.ConstantData.INSERT_VIDEOLIBUSERADDRESSMASTER);
			pst.setString(1, address.getAddrLine1());
			pst.setString(2, address.getAddrLine2());
			pst.setString(3, address.getAddrLine3());
			pst.setString(4, address.getTelNo());
			pst.setInt(5, cityid);
			pst.setInt(6, stateid);
			pst.setInt(7, countryid);
			pst.setInt(8, address.getZipCode());
			pst.setDate(9, sqlToday);
			pst.setInt(10, 1);
			pst.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}
		try {
			con = cn.getConnection();
			rs = null;
			pst = con.prepareStatement("select MAX(vlusraddress_id) from "
					+ constants.ConstantData.SCHEMA_NAME
					+ ".VideoLibUserAddressMaster");
			rs = pst.executeQuery();
			while (rs.next()) {
				maxAddrId = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			rs.close();
			pst.close();
			con.close();
			cn.getDisconnection();
		}
		return maxAddrId;
	}

	public String validPasswordChange(String emailid, String loginid,
			String Npassword) throws SQLException {
		java.util.Date today = new java.util.Date();
		java.sql.Date sqlToday = new java.sql.Date(today.getTime());
		String state = "false";
		Connection con = cn.getConnection();
		PreparedStatement pst = null;
		ResultSet rs = null;
		String TableEmailId = "";

		if (!Validation.validAlphaNumericString(loginid)) {
			System.out.println("Invalid LoginId");
			state = "Invalid Login ID";
		}
		if (!Validation.validEmailString(emailid)) {
			System.out.println("Invalid Email ID");
			state = "Invalid Email ID";
			return state;
		}
		if (!Validation.validPasswordString(Npassword)) {
			System.out.println("Invalid New Password");
			state = "Invalid New Password";
			return state;
		} else {
			try {
				pst = con
						.prepareStatement("select vlusr_emailid from "
								+ constants.ConstantData.SCHEMA_NAME
								+ ".VideoLibUsers where vlusr_loginid = ? and vlusr_rowstate != -1");
				pst.setString(1, loginid);
				rs = pst.executeQuery();
				while (rs.next()) {
					// String TableLoginId = rs.getString(1);
					TableEmailId = rs.getString(1);
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
			try {
				if (!TableEmailId.isEmpty()) {
					if (TableEmailId.equalsIgnoreCase(emailid)) {
						pst = con
								.prepareStatement("update "
										+ constants.ConstantData.SCHEMA_NAME
										+ ".VideoLibUsers set vlusr_password = ? , vlusr_lastdetailsupdate = ? where vlusr_loginid = ? and vlusr_emailid = ? and vlusr_rowstate != -1");
						pst.setString(1, Npassword);
						pst.setDate(2, sqlToday);
						pst.setString(3, loginid);
						pst.setString(4, emailid);
						pst.executeUpdate();
						state = "true";
					}
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				pst.close();
				con.close();
				cn.getDisconnection();
			}
		}
		return state;
	}

	public String[] listCountry() throws SQLException {
		Connection con = cn.getConnection();
		PreparedStatement pst = null;
		ResultSet rs = null;
		String[] countryList = new String[110];
		int i = 1;
		try {
			pst = con.prepareStatement("select vlc_countryname from "
					+ constants.ConstantData.SCHEMA_NAME
					+ ".VideoLibCountryMaster where vlc_rowstate != -1");
			rs = pst.executeQuery();
			while (rs.next()) {
				countryList[i] = rs.getString(1);
				i++;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			pst.close();
			con.close();
			cn.getDisconnection();
		}
		return countryList;
	}

	public String[] listState(String country) throws SQLException {
		Connection con = cn.getConnection();
		PreparedStatement pst = null;
		ResultSet rs = null;
		String[] stateList = new String[1000];
		int countryid = 0;
		int i = 1;

		try {
			pst = con
					.prepareStatement("select vlc_countryid from "
							+ constants.ConstantData.SCHEMA_NAME
							+ ".VideoLibCountryMaster where vlc_rowstate != -1 and vlc_countryname = ?");
			pst.setString(1, country);
			rs = pst.executeQuery();
			while (rs.next()) {
				countryid = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		try {
			pst = con
					.prepareStatement("select vls_statename from "
							+ constants.ConstantData.SCHEMA_NAME
							+ ".VideoLibStateMaster where vls_rowstate != -1 and vls_countryid = ?");
			pst.setInt(1, countryid);
			rs = pst.executeQuery();
			while (rs.next()) {
				stateList[i] = rs.getString(1);
				i++;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			pst.close();
			con.close();
			cn.getDisconnection();
		}
		return stateList;
	}

	public String[] listCity(String state) throws SQLException {
		Connection con = cn.getConnection();
		PreparedStatement pst = null;
		ResultSet rs = null;
		String[] cityList = new String[1000];
		int stateid = 0;
		int i = 1;

		try {
			pst = con
					.prepareStatement("select vls_stateid from "
							+ constants.ConstantData.SCHEMA_NAME
							+ ".VideoLibStateMaster where vls_rowstate != -1 and vls_statename = ?");
			pst.setString(1, state);
			rs = pst.executeQuery();
			while (rs.next()) {
				stateid = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		try {
			pst = con
					.prepareStatement("select vlcm_cityname from "
							+ constants.ConstantData.SCHEMA_NAME
							+ ".VideoLibCityMaster where vlcm_rowstate != -1 and vlcm_stateid = ?");
			pst.setInt(1, stateid);
			rs = pst.executeQuery();
			while (rs.next()) {
				cityList[i] = rs.getString(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			pst.close();
			con.close();
			cn.getDisconnection();
		}
		return cityList;
	}

	public Movie[] displayMovies(String filterAlphabet) throws SQLException {
		ArrayList<Movie> displayMovieDetails = new ArrayList<Movie>();
		// ArrayList<MovieCategory> movieCategory = new
		// ArrayList<MovieCategory>();
		Connection con = cn.getConnection();
		PreparedStatement pst = null;
		ResultSet rt = null;
		try {
			if (!con.isClosed()) {
				pst = con
						.prepareStatement("select mv.vlmov_id , mv.vlmov_name , mv.vlmov_production , mv.vlmov_releasedate , mct.vlmcat_id , mct.vlmcat_name , mv.vlmt_availablecopies from "
								+ constants.ConstantData.SCHEMA_NAME
								+ ".videolibmoviesmaster mv left outer join "
								+ constants.ConstantData.SCHEMA_NAME
								+ ".videolibmoviecategory mct on mv.vlmov_categoryid = mct.vlmcat_id "
								+ "where mv.vlmov_name like ? and mv.vlmov_rowstate != -1 and mct.vlmcat_rowstate != -1");
				String filterAlphabet1 = filterAlphabet + "%";
				pst.setString(1, filterAlphabet1);

				System.out.println(pst);
				rt = pst.executeQuery();
				while (rt.next()) {
					Movie movie = new Movie();
					MovieCategory movieCategory = new MovieCategory();
					movie.setMovieId(rt.getInt("mv.vlmov_id"));
					movie.setMovieName(rt.getString("mv.vlmov_name"));
					movie.setProdBanner(rt.getString("mv.vlmov_production"));
					movie.setReleaseDate(rt.getString("mv.vlmov_releasedate"));
					movieCategory
							.setMovieCategoryId(rt.getInt("mct.vlmcat_id"));
					movieCategory.setMovieCategory(rt
							.getString("mct.vlmcat_name"));
					movie.setMovieCategory(movieCategory);
					movie.setAvailableCopies(rt
							.getInt("mv.vlmt_availablecopies"));
					displayMovieDetails.add(movie);
				}// System.out.println("size in::::::::::::::::::::" +
					// displayMovieDetails.size());
			} /* this one is closing if brace */
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			pst.close();
			rt.close();
			con.close();
		}
		System.out.println("size in::::::::::::::::::::"
				+ displayMovieDetails.size());
		Movie moviesArray[] = new Movie[displayMovieDetails.size()];
		System.out.println("size in::::::::::::::::::::"
				+ displayMovieDetails.size());
		return displayMovieDetails.toArray(moviesArray);
	}

	public Movie[] latestMovies() throws SQLException {
		ArrayList<Movie> latestMovieDetails = new ArrayList<Movie>();
		Connection con = cn.getConnection();
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			if (!con.isClosed()) {

				pst = con
						.prepareStatement("select vlmov_id , vlmov_name , vlmt_availablecopies from "
								+ constants.ConstantData.SCHEMA_NAME
								+ ".videolibmoviesmaster "
								+ "where vlmov_releasedate between current_date and current_date + interval '1' month");
				rs = pst.executeQuery();
				while (rs.next()) {
					Movie movie = new Movie();
					movie.setMovieId(rs.getInt(1));
					movie.setMovieName(rs.getString(2));
					movie.setAvailableCopies(rs.getInt(3));
					latestMovieDetails.add(movie);
				}
			} /* this one is closing if brace */
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			pst.close();
			rs.close();
			con.close();
		}

		Movie moviesArray[] = new Movie[latestMovieDetails.size()];
		System.out.println("size=" + latestMovieDetails.size());
		return latestMovieDetails.toArray(moviesArray);
	}

	/**
	 * 
	 * @param movieCart
	 * @return
	 * @throws SQLException
	 */
	public MovieCart[] addToCart(MovieCart[] movieCart) throws SQLException {
		System.out.println("Into Add to Cart");
		java.util.Date today = new java.util.Date();
		java.sql.Date sqlToday = new java.sql.Date(today.getTime());
		Connection con = cn.getConnection();
		System.out.println("test");
		PreparedStatement pst[] = new PreparedStatement[movieCart.length];
		// int i = movieCart.length;
		try {
			System.out.println("test2");
			User user = new User();
			user.setId(movieCart[0].getUserId());
			user = userExistance(user);
			if (user.getErrorMsg() != "Success") {
				movieCart[0]
						.setErrorMsg("Registration issue kindly connect to administrator");
				return movieCart;
			}
			System.out.println("User authenticated!");
			movieCart = movieExistance(movieCart);
			System.out.println("Movie authenticated!");
			// for(i = 0 ; i <= movieCart.length ; i++){
			// if(movieCart[i].getErrorMsg() != null) return movieCart;
			// }
			movieCart = userMovieMapExistance(movieCart);
			System.out
					.println("Movie not already present! Going to insert in cart!");
			// for(i = 0 ; i <= movieCart.length ; i++){
			// if(movieCart[i].getErrorMsg() != null) return movieCart;
			// }
			for (int i = 0; i < movieCart.length
					&& movieCart[i].getErrorMsg() == null; i++) {
				// i = 0;
				// while ((i != movieCart.length)
				// && (movieCart[i].getErrorMsg() == null)) {
				pst[i] = con
						.prepareStatement(constants.ConstantData.INSERT_VIDEOLIBMOVIECART);
				pst[i].setInt(1, movieCart[i].getMovieId());
				pst[i].setInt(2, movieCart[i].getUserId());
				pst[i].setDate(3, sqlToday);
				pst[i].setString(4, "C");
				// pst[i].setInt(5, 1);
				pst[i].setDate(5, sqlToday);
				pst[i].setInt(6, 1);
				pst[i].setInt(7, 1); //Qty
				pst[i].executeUpdate();
				// i++;
				pst[i].close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			con.close();
		}
		System.out.println("Returning the updated cart array");
		return movieCart;
	}

	public User userExistance(User user) throws SQLException {
		String errorMsg = "Registration issue kindly connect to administrator";
		Connection con = cn.getConnection();
		PreparedStatement pst = null;
		ResultSet rs = null;
		int userid = 0;
		try {
			pst = con
					.prepareStatement("select vlusr_id from "
							+ constants.ConstantData.SCHEMA_NAME
							+ ".videolibusers where vlusr_id = ? and vlusr_rowstate != -1");
			pst.setInt(1, user.getId());
			rs = pst.executeQuery();
			while (rs.next())
				userid = rs.getInt("vlusr_id");
			if (userid == 0)
				user.setErrorMsg(errorMsg);
			else
				user.setErrorMsg("Success");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			pst.close();
			rs.close();
			con.close();
		}
		return user;
	}

	/**
	 * 
	 * @param movieCart
	 * @return
	 * @throws SQLException
	 */
	public MovieCart[] movieExistance(MovieCart[] movieCart)
			throws SQLException {
		String errorMsg = "Movie no longer available in the library";
		Connection con = cn.getConnection();
		// PreparedStatement[] pst = new PreparedStatement[movieCart.length];

		try {
			for (int i = 0; i < movieCart.length; i++) {
				System.out.println(i);
				ResultSet rs = null;
				PreparedStatement pst = con
						.prepareStatement("select vlmov_id , vlmt_availablecopies from "
								+ constants.ConstantData.SCHEMA_NAME
								+ ".videolibmoviesmaster where vlmov_id = ? and vlmov_rowstate != -1");
				pst.setInt(1, movieCart[i].getMovieId());
				rs = pst.executeQuery();
				while (rs.next()) {
					if (rs.getInt(2) <= 0)
						movieCart[i].setErrorMsg(errorMsg);
				}
				// while (!rs.next()) {
				// movieCart[i].setErrorMsg(errorMsg);
				// }
				pst.close();
				rs.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			con.close();
		}
		return movieCart;
	}

	/**
	 * 
	 * @param movieCart
	 * @return
	 * @throws SQLException
	 */
	public MovieCart[] userMovieMapExistance(MovieCart[] movieCart)
			throws SQLException {
		Connection con = cn.getConnection();
		PreparedStatement[] pst = new PreparedStatement[movieCart.length];
		ResultSet rs[] = new ResultSet[movieCart.length];
		try {
			for (int i = 0; i < movieCart.length; i++) {
				pst[i] = con
						.prepareStatement("select vlmt_cartstatus from "
								+ constants.ConstantData.SCHEMA_NAME
								+ ".VideoLibMovieCart where vlmt_movieid = ? and vlmt_userid = ? and vlmt_rowstate != -1 ");
				// pst[i] = con
				// .prepareStatement("select vlmt_movieid , vlmt_userid , vlmt_addedon , vlmt_cartstatus , vlmt_itemstatus from "
				// + constants.ConstantData.SCHEMA_NAME
				// +
				// ".VideoLibMovieCart where vlmt_movieid = ? and vlmt_userid = ? ((vlmt_cartstatus = 1 and vlmt_itemstatus = 'B') or (vlmt_cartstatus = 1 and vlmt_itemstatus)) and vlmt_rowstate != -1 ");
				pst[i].setInt(1, movieCart[i].getMovieId());
				pst[i].setInt(2, movieCart[i].getUserId());
				rs[i] = pst[i].executeQuery();
				while (rs[i].next()) {
					// if ((rs[i].getString(5).equals("B"))
					// && (rs[i].getInt(4) == 1))
					// movieCart[i]
					// .setErrorMsg("Movie is already available in your account.Kindly return it to store on time");
					// else if ((rs[i].getString(5).equals("C"))
					// && (rs[i].getInt(4) == 1))
					// movieCart[i]
					// .setErrorMsg("Movie is already added in your cart");
					// }

					if (rs[i].getString(1).equals("B"))
						movieCart[i]
								.setErrorMsg("Movie is already available in your account!");
					else if (rs[i].getString(5).equals("C"))
						movieCart[i]
								.setErrorMsg("Movie is already added in your cart");
				}
				pst[i].close();
				rs[i].close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			con.close();
		}

		return movieCart;
	}

	/**
	 * 
	 * @param user
	 * @return
	 * @throws SQLException
	 */
	public MovieCart[] retrieveCart(int userId) throws SQLException {
		ArrayList<MovieCart> movieCartArrayList = new ArrayList<MovieCart>();
		//ArrayList<MovieCart> movieCartArrayList = new ArrayList<MovieCart>();
		Connection con = cn.getConnection();
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			String query = "select vlmt_movieid, vlmt_qty from "
								+ constants.ConstantData.SCHEMA_NAME
								+ ".VideoLibMovieCart " +
					"where vlmt_userid = ? and vlmt_rowstate != -1 and vlmt_cartstatus = 'C'";
			// pst = con
			// .prepareStatement("select vlmt_movieid , vlmt_userid , vlmt_addedon from "
			// + constants.ConstantData.SCHEMA_NAME
			// +
			// ".VideoLibMovieCart where vlmt_userid = ? and vlmt_rowstate != -1 and vlmt_cartstatus = 'C'");
			pst = con.prepareStatement(query);
			pst.setInt(1, userId);
			rs = pst.executeQuery();
			while (rs.next()) {
				MovieCart movieCart = new MovieCart();
				movieCart.setMovieId(rs.getInt(1));
				movieCart.setQty(rs.getInt(2));
				movieCartArrayList.add(movieCart);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			con.close();
		}
		MovieCart[] movieCartArray = new MovieCart[movieCartArrayList.size()];
		return movieCartArrayList.toArray(movieCartArray);
	}
	
	/**
	 * 
	 * @param cartData
	 * @return
	 * @throws SQLException
	 */
	public Movie[] retrieveMovieDataForCart(MovieCart[] cartData) throws SQLException{
		Movie[] movieArray = new Movie[cartData.length];
		Connection con = cn.getConnection();
		PreparedStatement pst = null;
		ResultSet rs = null;
		try{
			for(int i=0; i<movieArray.length;i++){
				int movieId = cartData[i].getMovieId();
				String query = "select vlmov_name, vlmov_production, vlmov_rentamount from "
								+ constants.ConstantData.SCHEMA_NAME
								+ ".videolibmoviesmaster " +
						"where vlmov_id = ? and vlmov_rowstate != -1";
				pst = con.prepareStatement(query);
				pst.setInt(1, movieId);
				rs = pst.executeQuery();
				while (rs.next()) {
					Movie movie = new Movie();
					movie.setMovieName(rs.getString(1));
					movie.setProdBanner(rs.getString(2));
					movie.setRentAmount(rs.getInt(3));
					movieArray[i] = movie;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			con.close();
		}
		return movieArray;
		
	}

	/**
	 * 
	 * Function called after selection items for cart for the payment to check
	 * the availability of the items... Return Type:Integer list of movie IDs
	 * which are not available... for availability of all items the list will
	 * contain 0 ... List End point is the container with integer 0 in it.
	 */

	public int[] updateAvailableCopies(MovieCart[] movieCrt)
			throws SQLException {
		Connection con = cn.getConnection();
		PreparedStatement pst[] = new PreparedStatement[movieCrt.length];
		ResultSet[] rs = new ResultSet[movieCrt.length];
		int i = 0;
		ArrayList<Integer> itemNotAvailable = new ArrayList<Integer>();
		int[] itemIdNotAvailable = null;
		try {
			while (i != movieCrt.length) {
				pst[i] = con
						.prepareStatement("select vlmov_availablecopies from "
								+ constants.ConstantData.SCHEMA_NAME
								+ ".videolibmoviesmaster where vlmov_id = ? and vlmov_rowstate != -1");
				pst[i].setInt(1, movieCrt[i].getMovieId());
				rs[i] = pst[i].executeQuery();
				i++;
				pst[i].close();
				System.out.println(rs[i].getInt("vlmov_avaiablecopies"));
				if (rs[i].getInt("vlmov_avaiablecopies") > 0) {
					PreparedStatement pt = con
							.prepareStatement("update "
									+ constants.ConstantData.SCHEMA_NAME
									+ ".videolibmoviesmaster set vlmt_availablecopies = vlmt_availablecopies-1 , vlmov_lastupdateddate = now() where vlmov_id = ? and vlmov_rowstate != -1");
					pt.setInt(1, rs[i].getInt("vlmov_avaiablecopies"));
					pt.executeUpdate();
					pt.close();
				} else {
					itemNotAvailable.add(rs[i].getInt("vlmov_avaiablecopies"));
					rs[i].close();
				}
			}
			itemIdNotAvailable = new int[itemNotAvailable.size()];
			Iterator<Integer> iterator = itemNotAvailable.iterator();
			for (i = 0; i < itemIdNotAvailable.length; i++)
				itemIdNotAvailable[i] = iterator.next().intValue();
			itemIdNotAvailable[i] = 0;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			con.close();
		}
		return itemIdNotAvailable;
	}

	public boolean rollBackCartMovieMaster(MovieCart[] movieCrt) {
		boolean state = false;

		return state;
	}
	
	public MovieCategory[] fetchMovieCategory() {
		ArrayList<MovieCategory> movieCatArrList = new ArrayList<MovieCategory>();
		databaseConnection.Connection cn = new databaseConnection.Connection(); 
		Connection con = cn.getConnection();
		ResultSet rs;
		try {
		String query = "select vlmcat_id, vlmcat_name from videolibrary.videolibmoviecategory";
		PreparedStatement ps = con.prepareStatement(query);
		rs = ps.executeQuery();
		while (rs.next()) {
			//System.out.println("hi");
			MovieCategory movieCategory = new MovieCategory();
			movieCategory.setMovieCategoryId(rs.getInt(1));
			movieCategory.setMovieCategory(rs.getString(2));
			movieCatArrList.add(movieCategory);
		
		}
		ps.close();
		rs.close();
		con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		System.out.println("Movie Category array list size: "+movieCatArrList.size());
		MovieCategory[] movieCatArr = new MovieCategory[movieCatArrList.size()];
		return movieCatArrList.toArray(movieCatArr);
	}

	public User[] displayUser(int preferenceId)
	{

		databaseConnection.Connection cn = new databaseConnection.Connection();
		Connection con = cn.getConnection();
		ArrayList<model.User> users = new ArrayList<model.User>(); 

		try {
			if(!con.isClosed())
			{
				/*this case is for when user enters nothing and searches for 
				 * which we have to display all the users*/
				ResultSet rt = null;
				PreparedStatement pstmt = null;
				if(preferenceId==0)
				{
					System.out.println("inside all users");
					pstmt = con.prepareStatement("select * from videolibrary.VideoLibUsers where vlusr_rowstate !=-1 and vlusr_statusid = 1");

				} /*this one is for all users*/

				else if (preferenceId==1)
				{
					System.out.println("inside simple users");
					pstmt = con.prepareStatement("select * from videolibrary.VideoLibUsers where vlusr_rowstate !=-1 and vlusr_statusid = 1 and vlusr_prefernce=1");

				} /*this one is for simple user*/
				else if (preferenceId==2)
				{
					System.out.println("inside premium users");
					pstmt = con.prepareStatement("select * from videolibrary.VideoLibUsers where vlusr_rowstate !=-1 and vlusr_statusid = 1 and vlusr_prefernce=2");
				} /*this one is for premium user*/

				rt = pstmt.executeQuery();

				while(rt.next())
				{
					User user = new User();
					user.setId(rt.getInt(1));
					user.setSalutation(rt.getString(2));
					user.setFirstName(rt.getString(3));
					user.setMiddleName(rt.getString(4));
					user.setLastName(rt.getString(5));
					user.setDisplayName(rt.getString(6));
					user.setDob(String.valueOf(rt.getDate(7)));
					
					user.setSex(rt.getString(8));
					user.setLoginId(rt.getString(10));
					user.setLastPwdUpdate(String.valueOf(rt.getDate(12)));

					user.setEmailId(rt.getString(15));
					user.setLastLogin(String.valueOf(rt.getDate(17)));
					user.setSubscriptionRenewal(String.valueOf(rt.getDate(18)));
					users.add(user);

				} /* end of while*/
			} /* this one is for end of if*/
			/* this one is for if user enters particular userid*/

		} 
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		finally
		{
			cn.getDisconnection();
		}
		System.out.println("inside method size="+users.size());
		User[] userArray = new User[users.size()];
		userArray = (User[]) users.toArray(userArray);
		return userArray;


	} /*end of method for user*/


}

	





